﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.OracleClient;
using System.Net;
using System.Net.Mail;


/// <summary>
/// Summary description for DataConnection
/// </summary>
public class DataConnection
{
	public DataConnection()
	{
		//
		// TODO: Add constructor logic here
		//
	}
    public static string str;
    public static OracleConnection TaoKetNoi()
    {
        string chuoi = str;
        OracleConnection connection = new OracleConnection(chuoi);
        connection.Open();
        return connection;
    }

    private static OracleConnection con;
    private static OracleDataAdapter da;
    private static OracleCommand cmd;

    public static DataTable LayBang(string strSQL)
    {
        try
        {
            con = TaoKetNoi();
            da = new OracleDataAdapter(strSQL, con);
            DataTable dt = new DataTable();
            da.Fill(dt);
            
            return dt;
        }
        catch (Exception)
        {

            return null;
        }
        finally
        {
            if (da != null)
            {
                da.Dispose();
            }
            if (con != null)
            {
                con.Close();
            }
        }


    }
    public static DataTable LayBang_Thamso(string strSQL, OracleParameter[] thamso)
    {
        try
        {
            con = TaoKetNoi();
            cmd = new OracleCommand(strSQL, con);//update data

            for (int i = 0; i < thamso.Length; i++)
            {
                cmd.Parameters.Add(thamso[i]);
            }
            da = new OracleDataAdapter(cmd);
            int t = cmd.ExecuteNonQuery();
            DataTable dt = new DataTable();
            da.Fill(dt);
            return dt;
        }
        catch (Exception)
        {
            return null;
        }
        finally
        {
            if (cmd != null)
            {
                cmd.Dispose();
            }
            if (da != null)
            {
                da.Dispose();
            }
            if (con != null)
            {
                con.Close();
            }
        }
    }
    public static int TruyVan(string strSQL)
    {
        try
        {
            con = TaoKetNoi();
            OracleCommand cmd = new OracleCommand(strSQL, con);
            int sodong = cmd.ExecuteNonQuery();
            return sodong;
        }
        catch (Exception)
        {
            return -1;
        }
        finally
        {
            if (cmd != null)
            {
                cmd.Dispose();
            }
            if (da != null)
            {
                da.Dispose();
            }
            if (con != null)
            {
                con.Close();
            }
        }
    }


    public static int TruyVanMax(string strSQL)
    {
        int ma;
        try
        {
            con = TaoKetNoi();
            cmd = new OracleCommand(strSQL, con);

            try
            {
                
                ma = int.Parse(cmd.ExecuteScalar().ToString())+1;
            }
            catch
            {
                ma = 0;
            }
            return ma;
        }
        catch (Exception)
        {
            return -1;
        }
        finally
        {
            if (cmd != null)
            {
                cmd.Dispose();
            }
            if (da != null)
            {
                da.Dispose();
            }
            if (con != null)
            {
                con.Close();
            }
        }
    }




    public static int TruyVan_ThamSo(string storeProcedureName, OracleParameter[] thamso)
    {
        try
        {
            
            con = TaoKetNoi();
             cmd = new OracleCommand(storeProcedureName, con);
            cmd.CommandType = CommandType.StoredProcedure;
            for (int i = 0; i < thamso.Length; i++)
            {
                cmd.Parameters.Add(thamso[i]);
            }
            int sodong = cmd.ExecuteNonQuery();
            return sodong;
        }
        catch (Exception)
        {
            return -1;
        }
        finally
        {
            if (cmd != null)
            {
                cmd.Dispose();
            }
            if (da != null)
            {
                da.Dispose();
            }
            if (con != null)
            {
                con.Close();
            }
        }

    }

    public static int getPara(string storeProcedureName, OracleParameter[] para)
    {
        try
        {

             con = TaoKetNoi();
             cmd = new OracleCommand(storeProcedureName, con);
            cmd.CommandType = CommandType.StoredProcedure;
            for (int i = 0; i < para.Length; i++)
            {
                cmd.Parameters.Add(para[i]);
            }
            int sodong = cmd.ExecuteNonQuery();
            return sodong;
        }
        catch (Exception)
        {
            return -1;
        }
        finally
        {
            if (cmd != null)
            {
                cmd.Dispose();
            }
            if (da != null)
            {
                da.Dispose();
            }
            if (con != null)
            {
                con.Close();
            }
        }

    }


    public static DataTable GetDatatableByStoreProcedureNameCursor(string storeProcedureName, OracleParameter[] thamso)
    {
        try
        {
             con = TaoKetNoi();
             cmd = new OracleCommand(storeProcedureName, con);//update data
            cmd.CommandType = CommandType.StoredProcedure;
            for (int i = 0; i < thamso.Length; i++)
            {
                cmd.Parameters.Add(thamso[i]);
            }
            cmd.Parameters.Add("p_recordset", OracleType.Cursor).Direction = ParameterDirection.Output;
             da = new OracleDataAdapter(cmd);
            int t = cmd.ExecuteNonQuery();
            DataTable dt = new DataTable();
            da.Fill(dt);
            con.Close();
            return dt;
        }
        catch (Exception)
        {
            return null;
        }
        finally
        {
            if (cmd != null)
            {
                cmd.Dispose();
            }
            if (da != null)
            {
                da.Dispose();
            }
            if (con != null)
            {
                con.Close();
            }
        }
       
    }


    public static DataTable GetDatatableByStoreProcedureName(string storeProcedureName, OracleParameter[] thamso)
    {
        try
        {
             con = TaoKetNoi();
             cmd = new OracleCommand(storeProcedureName, con);//update data
            cmd.CommandType = CommandType.StoredProcedure;
            for (int i = 0; i < thamso.Length; i++)
            {
                cmd.Parameters.Add(thamso[i]);
            }
             da = new OracleDataAdapter(cmd);
            int t = cmd.ExecuteNonQuery();
            DataTable dt = new DataTable();
            da.Fill(dt);
            return dt;
        }
        catch (Exception)
        {
            return null;
        }
        finally
        {
            if (cmd != null)
            {
                cmd.Dispose();
            }
            if (da != null)
            {
                da.Dispose();
            }
            if (con != null)
            {
                con.Close();
            }
        }
    }


    public static DataTable GetDatatableByStoreProcedureNameNoParam(string storeProcedureName)
    {
        try
        {
             con = TaoKetNoi();
             cmd = new OracleCommand(storeProcedureName, con);//update data
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("p_recordset", OracleType.Cursor).Direction = ParameterDirection.Output;            
             da = new OracleDataAdapter(cmd);
            int t = cmd.ExecuteNonQuery();
            DataTable dt = new DataTable();
            da.Fill(dt);
            return dt;
        }
        catch (Exception)
        {
            return null;
        }
        finally
        {
            if (cmd != null)
            {
                cmd.Dispose();
            }
            if (da != null)
            {
                da.Dispose();
            }
            if (con != null)
            {
                con.Close();
            }
        }
    }






    public static bool SendMail_By_Gmail(string to, string strSubject, string strContent)
    {
        MailMessage mail = new MailMessage();

        mail.To.Add(to);
        mail.Subject = strSubject;
        mail.IsBodyHtml = true;
        mail.Body = strContent;
        mail.From = new MailAddress("it.mr.bill@gmail.com");
        try
        {
            SmtpClient client = new SmtpClient();
            client.DeliveryMethod = SmtpDeliveryMethod.Network;
            client.EnableSsl = true;
            client.Host = "smtp.gmail.com";
            client.Port = 587;
            // khai báo tài khoản và mat khẩu gừi mail
            NetworkCredential credentials = new NetworkCredential("it.mr.bill@gmail.com", "");
            client.UseDefaultCredentials = false;
            client.Credentials = credentials;
            client.Send(mail); // thuc hien gui mail
            return true;
        }
        catch (Exception ex)
        {
            Console.WriteLine("Loi " + ex.ToString());
            return false;
        }

    }






    public static DataSet GetDatasetByStoreProcedureName(string storeProcedureName)
    {
        try
        {
             con = TaoKetNoi();
             cmd = new OracleCommand(storeProcedureName, con);//update data
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("p_recordset", OracleType.Cursor).Direction = ParameterDirection.Output; 
             da = new OracleDataAdapter(cmd);
            int t = cmd.ExecuteNonQuery();
            DataSet ds = new DataSet();
            da.Fill(ds);
            return ds;
        }
        catch (Exception)
        {
            return null;
        }
        finally
        {
            if (cmd != null)
            {
                cmd.Dispose();
            }
            if (da != null)
            {
                da.Dispose();
            }
            if (con != null)
            {
                con.Close();
            }
        }
    }

   
   
}
